﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Linq;
using System.Text;
using System.IO;
using System.Data.SqlClient;
//using System.Data.SqlServerCe;
using System.Data;
using System.Data.OleDb;
//using ErikEJ.Sql;



namespace PAPI_Import
{
    partial class Program
    {
        static void OperateWorkMode2()
        {
            SqlConnection SqlConn = new SqlConnection(sSqlConnectionString);

            string sClearSQL = @"   SELECT t1.Cost_Cent,t1.Int_Order,t1.PONumber,t1.Item,t2.First_Name,t2.Last_Name,t2.email,t1.DeliveryDate
                                    FROM POes t1
                                    INNER JOIN Papi_Users t2 ON t2.Requisitio=t1.Requisitioner
                                    WHERE t1.latest=1 AND t1.Status<99";

            SqlCommand SqlCmd = new SqlCommand(sClearSQL, SqlConn);
            SqlConn.Open();
            SqlDataReader dr = SqlCmd.ExecuteReader();

            int counter = 0;

            while (dr.Read())
            {
                Int64 sPONUmber = dr.GetInt64(dr.GetOrdinal("PONUmber"));
                int sItem = dr.GetInt32(dr.GetOrdinal("Item"));
                String sFirstName = dr.GetString(dr.GetOrdinal("First_Name"));
                String sLastName = dr.GetString(dr.GetOrdinal("Last_Name"));
                String sEmail = dr.GetString(dr.GetOrdinal("Email"));
                DateTime dDeliveryDate = dr.GetDateTime(dr.GetOrdinal("DeliveryDate"));
                String sCost_Cent = dr.GetString(dr.GetOrdinal("Cost_Cent"));
                String sInt_Order = dr.GetString(dr.GetOrdinal("Int_Order"));

                TimeSpan dateDiff = dDeliveryDate.Subtract(DateTime.Today);

                if (-1 < dateDiff.Days && dateDiff.Days <= 7) //Notify Owner
                {
                    counter++;
                    PapiImportSendMail(MailType.WarningMail, sEmail, "Message from POMP System", "Hello " + 
                        sFirstName + " " + sLastName + ", " + Environment.NewLine + Environment.NewLine +
                    "Delivery date for PO Number: " + sPONUmber + ", item: " + sItem + " is near- " + dDeliveryDate.ToShortDateString() + " (" + dateDiff.Days + " day(s) from now). Please handle it." +
                    Environment.NewLine + Environment.NewLine + "Thank you, "  + Environment.NewLine + "POMP System",true);
                }
                else if (-7 < dateDiff.Days && dateDiff.Days <= -1) // Notify Int.Order
                {
                        counter++;
                        String Send_To;

                        if (sInt_Order != "") //send to int order...
                        {
                            try
                            {
                                Send_To = getEmailForCCorIO(UserType.Int_Order, sInt_Order);
                            }
                            catch (NoUsersException e)
                            {
                                dr.Close();
                                SqlConn.Close();
                                writeToLog("Error: Missing Int_Order user for " + sInt_Order + ". Exiting.");
                                return;
                            }
                            
                        }
                        else //send to cost center
                        {
                            try
                            {
                                Send_To = getEmailForCCorIO(UserType.Cost_Center, sCost_Cent);
                            }
                            catch (NoUsersException e)
                            {
                                dr.Close();
                                SqlConn.Close();
                                writeToLog("Error: Missing Cost_Cent user for " + sCost_Cent + ". Exiting.");
                                return;                                
                            }
                        }

                        PapiImportSendMail(MailType.WarningMail, Send_To,
                            "Hello " + (sInt_Order != "" ? "Internal Order manager" : "Cost Center") + ", " + Environment.NewLine + Environment.NewLine +
                        "Delivery date for an item under your responsibility has passed (" + dDeliveryDate.ToShortDateString() + ") by " + (-dateDiff.Days) + " day(s). Please handle it." + Environment.NewLine +
                        "PO Number: " + sPONUmber + ", Item: " + sItem + 
                        Environment.NewLine + Environment.NewLine + "Thank you, "  + Environment.NewLine + "POMP System",true);
                }
                else if (dateDiff.Days <= -7) // Notify Cost.Order
                {
                    counter++;
                    String Send_To;

                    try
                    {
                        Send_To = getEmailForCCorIO(UserType.Cost_Center, sCost_Cent);
                    }
                    catch (NoUsersException e)
                    {
                        dr.Close();
                        SqlConn.Close();
                        writeToLog("Error: Missing Cost_Cent user for " + sCost_Cent + ". Exiting.");
                        return;                                
                    }
                    
                    PapiImportSendMail(MailType.WarningMail, Send_To, 
                    "Hello Cost Center, " + Environment.NewLine + Environment.NewLine +
                    "Delivery date for an item under your responsibility has passed (" + dDeliveryDate.ToShortDateString() + ") by " + (-dateDiff.Days) + " day(s). Please handle it." + Environment.NewLine +
                    "PO Number: " + sPONUmber + ", item: " + sItem + 
                    Environment.NewLine + Environment.NewLine + "Thank you, "  + Environment.NewLine + "POMP System",true);
                }
            }

            dr.Close();
            SqlConn.Close();

            writeToLog("Reminder sent to " + counter + " relevant users regarding Delivery Dates.");

            return;
        }

    
    
    }
}
